﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using DBUtil;

namespace OutPutPermissions_App
{
    class Program
    {
        static void Main(string[] args)
        {
            //BaseMenu();

            //---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


            //GetItemNames();

            OutPutTestData();






        }



        static List<string> lstStr = new List<string>()
                {
                    "凝结时间,min-初凝  ≥45",
                    "凝结时间,min-终凝  ≤600",
                    "安定性（雷氏夹沸煮后增加距离）,mm  ≤5.0",
                    "抗折强度（3d）,MPa  ≥3.5",
                    "抗折强度（28d）,MPa  ≥6.5",
                    "抗压强度（3d）,MPa  ≥17.0",
                    "抗压强度（28d）,MPa  ≥42.5"
                };
        private static void OutPutTestData()
        {

            string init_sql = @"select p.id 样品序号,psn.SelectNO 报告编号,p.ProductName 样品名称,dbo.getAllStandardTag(p.id,',') 规格,tu1.Name 受检单位,tu2.Name as 厂家单位 from Product p
left join ProductSelectNO psn on p.ID = psn.Product_ID left join TrustUnit tu1 on tu1.ID = p.BeCheckUnit_ID left join TrustUnit tu2 on tu2.ID = p.FactoryUnit_ID where 1=1 ";

            var dictLike = new Dictionary<string, string>();
            dictLike.Add("and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%水泥%' order by p.id asc", "水泥");
            dictLike.Add("and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%砂%' order by p.id asc", "砂");
            dictLike.Add("and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%石%' order by p.id asc", "石");
            dictLike.Add("and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%灰%' order by p.id asc", "粉煤灰");
            dictLike.Add("and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%矿%' order by p.id asc", "矿粉");
            dictLike.Add("and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%剂%' order by p.id asc", "剂");

            List<string> lstLike = new List<string>()
            {
"and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%水泥%' order by p.id asc",
"and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%砂%' order by p.id asc",
"and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%石%' order by p.id asc",
"and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%灰%' order by p.id asc",
"and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%矿%' order by p.id asc",
"and psn.SelectNO like '%JCJW2015HNT%' and p.ProductName like '%剂%' order by p.id asc"
            };


            DBSession db = new DBSession(DataBaseType.MsSql);
            foreach (var whSql in lstLike)
            {
                string qy_sql = init_sql + whSql;
                DataTable data = db.QueryTable(qy_sql, 30000);
                data.TableName = dictLike[whSql];
                ExecOutPut(data);
            }

        }

        public static void ExecOutPut(DataTable data)
        {
            var lstDC = lstStr.ConvertAll(t => new DataColumn(t));
            DBSession db = new DBSession(DataBaseType.MsSql);

            data.Columns.AddRange(lstDC.ToArray());

            DataTable dataNew = data.Copy();
            List<int> lstDelIndex = new List<int>();
            for (int j = 0; j < data.Rows.Count; j++)
            {
                DataRow dr = data.Rows[j];

                string cx_sql = @"select * from (select (case when 子项目 is null or len(子项目)<=0 then 项目+'  '+要求 else 项目+'-'+子项目+'  '+要求 end) 项目,结果 
from getProductTestTab({0})) tt where 项目 in (
'凝结时间,min-初凝  ≥45',
'凝结时间,min-终凝  ≤600',
'安定性（雷氏夹沸煮后增加距离）,mm  ≤5.0',
'抗折强度（3d）,MPa  ≥3.5',
'抗折强度（28d）,MPa  ≥6.5',
'抗压强度（3d）,MPa  ≥17.0',
'抗压强度（28d）,MPa  ≥42.5'
)";
                cx_sql = string.Format(cx_sql, dr["样品序号"].ToString());
                var dict = DBUtil.DBUtil.GetDict<string, string>(db.QueryTable(cx_sql, 3000), "项目", "结果");
                if (dict.Count == lstStr.Count && lstStr.Contains(dict.FirstOrDefault().Key))
                {
                    foreach (var item in dict)
                    {
                        dataNew.Rows[j][item.Key] = item.Value;
                    }
                }
                else
                {
                    lstDelIndex.Add(j);
                }
            }

            foreach (var index in lstDelIndex)
            {
                dataNew.Rows.RemoveAt(index);
            }
            lstDelIndex.Clear();

            string path = Path.GetFullPath("testdata");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            path = Path.Combine(path, "样品实验结果-" + dataNew.TableName + ".xlsx");
            bool isOk = NPOIHelper.ToExcel(dataNew, path);
        }




        public static void GetItemNames()
        {
            string sel_sql = @"select p.id from Product p left join ProductSelectNO psn on p.ID = psn.Product_ID where 1=1 and psn.SelectNO like '%JCJW2015HNT%' or psn.SelectNO like '%JCJW2016HNT%' order by p.id asc";
            DBSession db = new DBSession(DataBaseType.MsSql);
            DataTable data = db.QueryTable(sel_sql, 300);
            var lstIds = DBUtil.DBUtil.GetFirstCol<string>(data);
            List<string> lstSql = new List<string>();
            foreach (string id in lstIds)
            {
                string new_sql = "select " + id + " as 样品序号, (case when 子项目 is null or len(子项目)<=0 then 项目 else 项目+'-'+子项目 end) 项目,要求,结果 from getProductTestTab(" + id + ")";
                lstSql.Add(new_sql);
            }
            string total_sql = string.Join(" union all ", lstSql);
            DataTable dataNew = db.QueryTable(total_sql, 30000);

            string path = Path.GetFullPath("testdata");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            path = Path.Combine(path, "样品实验结果.xlsx");
            bool isOk = NPOIHelper.ToExcel(dataNew, path);
        }

        //---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        private static void BaseMenu()
        {
            bool isOk = false;
            string sel_sql = @"Select c.Id,c.menuname As ParentName,b.id,b.menuname,a.id,a.menuname As ChildName
from Base_Menu a
Left Join Base_Menu b On b.id=a.ParentID 
Left Join Base_Menu c On c.id=b.parentid
Where c.Id Is Not Null
order By c.menucode, c.menuname,b.menucode,b.menuname,a.menucode,a.menuname Asc";

            sel_sql = @"Select c.menuname 模块,b.menuname 页面,a.menuname 操作,a.id as ID
from Base_Menu a 
Left Join Base_Menu b On b.id=a.ParentID 
Left Join Base_Menu c On c.id=b.parentid
Where c.Id Is Not Null  And c.menuname Not In ( '系统管理', '研发人员维护')
order By c.menucode, c.menuname,b.menucode,b.menuname,a.menucode,a.menuname Asc";

            DBSession db = new DBSession(DataBaseType.Oracle);
            DataTable data = db.QueryTable(sel_sql, 300);

            Hashtable htData = new Hashtable();

            var dict = GetDictHt();

            foreach (var item in dict)
            {
                string parentName = item.Key;
                Hashtable ht = item.Value;

                DataTable data_new = data.Copy();
                foreach (DictionaryEntry childItem in ht)
                {
                    string columnName = childItem.Value.ToString();
                    DataColumn dc = new DataColumn(columnName);
                    data_new.Columns.Add(dc);

                    string IdNum = childItem.Key.ToString();
                    sel_sql = "Select distinct menuid From BASE_USERMENURELATION a Where a.userid='{0}'";
                    sel_sql = string.Format(sel_sql, IdNum);
                    List<string> lstMenuId = DBUtil.DBUtil.GetFirstCol<string>(db.QueryTable(sel_sql));

                    for (int j = 0; j < data.Rows.Count; j++)
                    {
                        string curMenuId = data.Rows[j]["Id"].ToString();
                        if (lstMenuId.Contains(curMenuId))
                        {
                            data_new.Rows[j][columnName] = "√";
                        }
                    }
                }

                if (!Directory.Exists(Path.GetFullPath("output")))
                {
                    Directory.CreateDirectory(Path.GetFullPath("output"));
                }
                string xlsPath = Path.GetFullPath("output/新系统人员权限-" + parentName + ".xlsx");
                NPOIHelper.DataTree(xlsPath, data_new, 2);
            }

        }



        static Dictionary<string, Hashtable> GetDictHt()
        {
            bool isOk = false;

            Dictionary<string, Hashtable> dict = new Dictionary<string, Hashtable>(StringComparer.OrdinalIgnoreCase);
            string sel_sql = @"Select b.deptname parentName,Concat(nvl(a.realName,a.loginName),Concat('☆',a.loginName)) ChildName,a.id From Base_Users a 
Left Join Base_Department b On b.id= a.departmentid
Where nvl(a.realName,a.loginName) <>'管理员'
Order By b.deptcode,b.deptname Asc";
            DBUtil.DBSession db = new DBSession(DataBaseType.Oracle);
            DataTable data = db.QueryTable(sel_sql);

            //string xlsPath = Path.GetFullPath("部门人员.xls");
            //isOk = NPOIHelper.ToExcel(data, xlsPath);

            Hashtable ht = new Hashtable(StringComparer.OrdinalIgnoreCase);
            foreach (DataRow dr in data.Rows)
            {
                string deptName = dr["parentName"].ToString();
                string realName = dr["ChildName"].ToString();
                string uid = dr["id"].ToString();
                if (dict.TryGetValue(deptName, out ht))
                {
                    dict.Remove(deptName);
                    ht.Add(uid, realName);
                    dict.Add(deptName, ht);
                }
                else
                {
                    ht = new Hashtable(StringComparer.OrdinalIgnoreCase);
                    ht.Add(uid, realName);
                    dict.Add(deptName, ht);
                }
            }
            return dict;
        }
    }
}
